package com.yanqu.road.dataHelper.utils;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

public class ExcelData {
    private XSSFSheet sheet;

    /**
     * 构造函数，初始化excel数据
     * @param filePath  excel路径
     * @param sheetName sheet表名
     */
    public ExcelData(String filePath,String sheetName){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
            //获取sheet
            sheet = sheets.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row
     * @param column
     * @return
     */
    public String getExcelDateByIndex(int row,int column){
        XSSFRow row1 = sheet.getRow(row);
        String cell = row1.getCell(column).toString();
        return cell;
    }

    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     * @param caseName
     * @param currentColumn 当前单元格列的索引
     * @param targetColumn 目标单元格列的索引
     * @return
     */
    public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
        String operateSteps="";
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            XSSFRow row = sheet.getRow(i);
            String cell = row.getCell(currentColumn).toString();
            if(cell.equals(caseName)){
                operateSteps = row.getCell(targetColumn).toString();
                break;
            }
        }
        return operateSteps;
    }

    //打印excel数据
    public void readExcelData(){
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                String cell = row.getCell(j).toString();
                System.out.println(cell);
            }
        }
    }

    public Map<String,Map<String,String>> getExcelData(){
        Map<String,Map<String,String>> dealData = new ConcurrentHashMap<>();
        List<String> keyList = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int columns = row.getPhysicalNumberOfCells();
            //第一行是名称
            if(i == 0){
                for(int j=0;j<columns;j++){
                    keyList.add(j,row.getCell(j).toString());
                }
            }
            Map<String,String> rowData = new ConcurrentHashMap<>();
            for(int j=0;j<columns;j++){
                if (row.getCell(j) == null) {
                    break;
                }
                if("".equals(row.getCell(j).toString())){
                    break;
                }
                CellType cellType = row.getCell(j).getCellTypeEnum();
                String cell = "";
                if(cellType == CellType.NUMERIC){
                    cell = Long.toString(new Double(row.getCell(j).getNumericCellValue()).longValue());
                }else{
                    cell = row.getCell(j).getStringCellValue();
                }
                rowData.put(keyList.get(j),cell);
            }
            dealData.put(i+"",rowData);
        }
        dealData.remove("0");
        return dealData;
    }

    private static void makeDir(){
        String path = System.getProperty("user.dir") + File.separator + "outText";
        File f = new File(path);
        if(!f.exists()){
            f.mkdirs(); //创建目录
        }
    }

    public static void outExcelData(String fileName,List<String> keyList,List<Map<String,String>> outData) throws IOException {
        makeDir();
        //创建工作薄对象
        HSSFWorkbook workbook = new HSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0,"sheet0");//设置sheet的Name
        //创建工作表的行
        HSSFRow row = sheet.createRow(0);//设置第一行，从零开始
        for(int i=0;i<keyList.size();i++){
            row.createCell(i).setCellValue(keyList.get(i));//第一行第三列为aaaaaaaaaaaa
        }
        for(int j=0;j<outData.size();j++){
            row = sheet.createRow(j+1);
            for(int i=0;i<keyList.size();i++){
                row.createCell(i).setCellValue(outData.get(j).get(keyList.get(i)));
            }
        }
        //文档输出
        String dirPath = System.getProperty("user.dir") + File.separator;
        FileOutputStream out = new FileOutputStream(dirPath + "outText" + File.separator + fileName + "_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
        workbook.write(out);
        out.close();
    }

    //创建Excel对象
    public static void testExcel2() throws IOException {
        //创建工作薄对象
        HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        //创建工作表的行
        HSSFRow row = sheet.createRow(0);//设置第一行，从零开始
        row.createCell(2).setCellValue("aaaaaaaaaaaa");//第一行第三列为aaaaaaaaaaaa
        row.createCell(0).setCellValue(new Date());//第一行第一列为日期
        workbook.setSheetName(0,"sheet的Name");//设置sheet的Name

        //文档输出
        String dirPath = System.getProperty("user.dir") + File.separator;
        FileOutputStream out = new FileOutputStream(dirPath + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
        workbook.write(out);
        out.close();
    }

    //测试方法
    public static void main(String[] args){

        String dirPath = System.getProperty("user.dir") + File.separator;
        ExcelData sheet0 = new ExcelData(dirPath + "inExcel" + File.separator + "pass.xlsx", "sheet0");
        //获取第4行第1列
        String cell2 = sheet0.getExcelDateByIndex(3, 0);
        //根据第3列值为“customer23”的这一行，来获取该行第2列的值
//        String cell3 = sheet1.getCellByCaseName("customer23", 2,1);
        System.out.println(cell2);
//        System.out.println(cell3);
        sheet0.getExcelData();
        try {
            testExcel2();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
